System and method for enhanced query optimizer search space ordering

ABSTRACT

In an optimizer within a Relational Database Management System improved ways in which a search space (the universe of possible join combinations) is generated and managed and improved ways in which the elements of a search space are evaluated so that among other things unpromising elements are efficiently dropped (pruned).

BACKGROUND OF THE INVENTION

1. Field of the Invention

The present invention relates generally to information processingenvironments. More particularly, the present invention relates tocapabilities that enhance substantially the operation, effectiveness,efficiency, etc. of query optimizers that are found in DatabaseManagement Systems (DBMSs).

2. Background Art

A common element of an information processing environment is a database,which is in effect a computer-based repository of information. Databasesare extraordinarily prevalent and may be found on almost any computingplatform including inter alia mainframe computers; computer servers;Personal Computers (PCs); handheld computers; pagers; Personal DigitalAssistants (PDAs); cellular telephones, smart phones, and other wirelessdevices; radios; TVs; navigation systems; automobile audio systems; netappliances; etc.

A DBMS serves as a something of a ‘bridge’ between the information in adatabase (handling inter alia the organization of the information, thestorage of the information on different devices, etc.) and users of thedatabase. Among other things a DBMS provides database users with alogical or conceptual view of a database, allowing them to not concernthemselves with inter alia the physical, implementation, etc.particulars of the database. When a user wishes to perform some actionon the database (e.g., to retrieve a piece of information from thedatabase, to update a piece of information in the database, to add a newpiece of information to the database, etc.) the user will typicallysubmit a query to the DBMS.

A database may be organized according to different models such ashierarchical, network, and relational.

Under a relational model a database may comprise inter alia one or moretables (relations), each table comprising one or more rows or records(tuples), each row/record comprising one or more columns or fields(attributes), with each column/field comprising some piece ofinformation. As an example, a database comprising information on anorganization's employees might contain a table EMPLOYEES that houses onerecord for each employee. Each record in the EMPLOYEES table mightcontain fields that preserve specifics about the employee such as interalia the employee's name (e.g., a field named EMP_NAME), home address(e.g., a field named EMP_ADDRESS), current position, salary, worktelephone number, etc.

Under a relational model a ‘bridge’ DBMS takes the form of a RelationalDBMS (RDBMS) and a query to a RDBMS typically takes the form of aStructured Query Language (SQL) statement.

A SQL statement (such as for example ‘SELECT EMP_NAME, EMP_ADDRESS FROMEMPLOYEES’) expresses a desired result (in the instant example, ‘pleasereturn to me the name and the address of each employee’) but does notinter alia identify how those results should be obtained. In otherwords, the query itself does not specify how the query should beevaluated by an RDBMS. A component of an RDBMS, a query optimizer oroptimizer, is responsible for inter alia (1) identifying the differentvalid ways in which (plans for how) the data within the database may beaccessed so as to achieve the result that is requested by a SQLstatement, (2) evaluating and costing the identified plans, and (3)selecting the ‘best’ (e.g., the cheapest, the fastest, etc.) plan.

As it completes its work a query optimizer may identify and evaluate anumber of items, artifacts, criteria, etc. including inter alia joinoperations.

Classically a RDBMS supports dyadic join operations, that is joinoperations that involve just two entities such as tables (e.g., the joinoperation T₁

T₂ involving the two tables T₁ and T₂). Consequently for a query thatrequires an n-way join (i.e., a join operation that involves n tableswhere n>2) a query optimizer must inter alia enumerate or identify(possibly just some subset of) the universe of possible joincombinations (i.e., a search space); evaluate, based on various criteriaincluding for example cost, some or all of the candidates in the searchspace; and then string together one specific sequence of individualtwo-way join operations to arrive at the ‘best’ (e.g., perhaps thecheapest) way of realizing the required n-way join. For example, for afour-way join involving four tables (T₁, T₂, T₃ and T₄) a queryoptimizer might arrive at the specific join sequence ((T₁

T₂)

(T₃

T₄)).

Conventional query optimization techniques often give rise to variousdisadvantages. For example:

1) As the number of entities (e.g., tables) in an n-way join increasesthe size of the resulting search space, that is the universe of possiblejoin combinations, grows very quickly resulting in inter alia longer andlonger amounts of time to iterate through the elements of the searchspace (to for example access, review, cost, etc. those elements).

2) For many dynamically generated queries the execution time of thequery itself may be quite small but the optimization time may be quitelarge and thus disproportionate to the execution time.

From all of the different plans that an optimizer may have to chosefrom, if it selects a ‘good’ plan then processing of the query will becompleted ‘quickly’ (with possibly inter alia lower system resourceconsumption, etc.). Alternatively, if it selects a ‘bad’ plan thenprocessing of the query will be completed ‘slowly’ (with possibly interalia higher system resource consumption, etc.).

Given the performance, system resource consumption, etc. ramificationsand implications of the query optimization process it is obviously veryimportant for an optimizer to identify and select the ‘best’ availablequery execution plan. That objective—identifying and selecting the‘best’ available query execution plan—is made challenging by the host ofconstraints that an optimizer must operate under including inter aliaavailable system resources (such as memory), specific querycharacteristics, parameters such as the maximum amount of time that anoptimizer may spend on any particular activity, the status of the RDBMS,etc.

Aspects of the present invention address the challenge that was notedabove (1) by (a) improving upon the way in which a search space isgenerated and managed and (b) improving on the way in which the elementsof a search space are evaluated so that among other things unpromisingelements are efficiently dropped (pruned) (2) while addressing, in newand innovatory ways, various of the not insubstantial challenges thatare associated with same.

SUMMARY OF THE INVENTION

In one embodiment of the present invention there is provided aserver-based method for enhanced query optimizer search space managementcomprising (1) saving, uncosted and in an ordered fashion, a subset ofthe enumerated partitions that are generated by a join enumerationalgorithm in a memoization construct, (2) generating a plan and costingthe plan for a subset of the saved partitions, such that unpromisingpartitions are pruned, and (3) selecting one of the costed plans.

Further features and advantages of the present invention, as well as thestructure and operation of various embodiments thereof, are described indetail below with reference to the accompanying drawings. It is notedthat the invention is not limited to the specific embodiments describedherein. Such embodiments are presented herein for illustrative purposesonly. Additional embodiments will be apparent to persons skilled in therelevant art(s) based on the teachings contained herein.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 depicts an exemplary computer system through which embodiments ofaspects of the present invention may be implemented.

FIG. 2 illustrates an exemplary computer software environment that mayamong other things direct the operation of aspects of FIG. 1's computersystem.

FIG. 3 illustrates aspects of an exemplary information processingenvironment.

FIG. 4 illustrates an ordered-Par( ) algorithm that encapsulates aspectsof the present invention.

FIG. 5 illustrates one possible EnumeratePartition( ) algorithm.

FIG. 6 illustrates one possible GenerateBestPlan( ) algorithm.

FIG. 7 captures the results of a particular query being optimizedthrough two different algorithms.

FIG. 8 captures two specific results from a particular query beingoptimized through two different algorithms.

FIGS. 9→11 capture two specific results from a particular query havingthree different shapes being optimized through different algorithms.

The features and advantages of the present invention will become moreapparent from the detailed description set forth below when taken inconjunction with the above identified drawings.

Throughout the drawings (a) like reference numbers generally indicateidentical, functionally similar, and/or structurally similar elementsand (b) the left-most digit(s) of a reference number generally identifythe drawing in which the reference number first appears. For example, inFIG. 6 reference numeral 504 would direct the reader to FIG. 5 for thefirst appearance of that item.

It will be understood that the drawings identified above depictembodiments of the invention. Variations of these embodiments will bereadily apparent to persons skilled in the relevant art(s) based on theteachings contained herein.

DETAILED DESCRIPTION OF THE INVENTION

Embodiments of the present invention are described herein in the contextof a method and apparatus for enhanced query optimizer search spaceordering. Those of ordinary skill in the relevant art will realize thatthe following detailed description of the present invention isillustrative only and is not intended to be in any way limiting. Otherembodiments of the present invention will readily suggest themselves tosuch skilled persons having the benefit of this disclosure. Referencewill now be made in detail to implementations of the present inventionas illustrated in the accompanying drawings and as described below.

In the interest of clarity, not all of the routine features of theimplementations described herein are shown and described. It will, ofcourse, be appreciated that in the development of any such actualimplementation, numerous implementation-specific decisions must be madein order to achieve the developer's specific goals, such as compliancewith application- and business-related constraints, and that thesespecific goals will vary from one implementation to another and from onedeveloper to another. Moreover, it will be appreciated that such adevelopment effort might be complex and time-consuming, but wouldnevertheless be a routine undertaking of engineering for those ofordinary skill in the art having the benefit of this disclosure.

Various aspects of the present invention may be implemented by software,firmware, hardware, or any combination thereof. FIG. 1 illustrates anexample computer system 100 in which the present invention or portionsthereof (such as for example described below under paragraphs 0050-0088)can be implemented as computer-readable code. Various embodiments of theinvention are described in terms of this example computer system 100.After reading this description, it will become apparent to a personskilled in the relevant art how to implement the invention using othercomputer systems and/or computer architectures.

Computer system 100 includes one or more processors, such as processor104. Processor 104 can be a special purpose processor or a generalpurpose processor. Processor 104 is connected to a communicationinfrastructure 102 (for example, a bus or a network).

Computer system 100 also includes a main memory 106, preferably RandomAccess Memory (RAM), containing possibly inter alia computer softwareand/or data 108.

Computer system 100 may also include a secondary memory 110. Secondarymemory 110 may include, for example, a hard disk drive 112, a removablestorage drive 114, a memory stick, etc. A removable storage drive 114may comprise a floppy disk drive, a magnetic tape drive, an optical diskdrive, a flash memory, or the like. A removable storage drive 114 readsfrom and/or writes to a removable storage unit 116 in a well knownmanner. A removable storage unit 116 may comprise a floppy disk,magnetic tape, optical disk, etc. which is read by and written to byremovable storage drive 114. As will be appreciated by persons skilledin the relevant art(s) removable storage unit 116 includes a computerusable storage medium 118 having stored therein possibly inter aliacomputer software and/or data 120.

In alternative implementations, secondary memory 110 may include othersimilar means for allowing computer programs or other instructions to beloaded into computer system 100. Such means may include, for example, aremovable storage unit 124 and an interface 122. Examples of such meansmay include a program cartridge and cartridge interface (such as thatfound in video game devices), a removable memory chip (such as anErasable Programmable Read-Only Memory [EPROM], or ProgrammableRead-Only Memory [PROM]) and associated socket, and other removablestorage units 124 and interfaces 122 which allow computer softwareand/or data to be transferred from the removable storage unit 124 tocomputer system 100.

Computer system 100 may also include an input interface 126 and a rangeof input devices 128 such as, possibly inter alia, a keyboard, a mouse,a track ball, a pointing device, etc.

Computer system 100 may also include an output interface 130 and a rangeof output devices 132 such as, possibly inter alia, a display monitor,one or more speakers, a printer, etc.

Computer system 100 may also include a communications interface 134.Communications interface 134 allows computer software and/or data 138 tobe transferred between computer system 100 and external devices.Communications interface 134 may include inter alia a modem, a networkinterface (such as inter alia an Ethernet card), a communications port,a Personal Computer Memory Card International Association (PCMCIA) slotand card, or the like. Computer software and/or data 138 transferred viacommunications interface 134 are in the form of signals 136 which may beelectronic, electromagnetic, optical, or other signals capable of beingreceived by communications interface 134. These signals 136 are providedto communications interface 134 via a communications path 140.Communications path 140 carries signals and may be implemented usingwire or cable, fiber optics, a phone line, a cellular phone link, aRadio Frequency (RF) link or other communications channels.

As used in this document, the terms “computer program medium,” “computerusable medium,” and “computer readable medium” generally refer to mediasuch as removable storage unit 116, removable storage unit 124, and ahard disk installed in hard disk drive 112. Signals carried overcommunications path 140 can also embody the logic described herein.Computer program medium and computer usable medium can also refer tomemories, such as main memory 106 and secondary memory 110, which can bememory semiconductors (e.g. Dynamic Random Access Memory [DRAM]elements, etc.). These computer program products are means for providingcomputer software to computer system 100.

Computer programs or software (also called computer control logic) arestored in main memory 106 and/or secondary memory 110. Computer programsmay also be received via communications interface 134. Such computerprograms, when executed, enable computer system 100 to implement thepresent invention as discussed herein. In particular, the computerprograms, when executed, enable processor 104 to implement the processesof aspects of the present invention, such as for example the stepsdiscussed below under paragraphs 0050-0088. Accordingly, such computerprograms represent controllers of the computer system 100. Where theinvention is implemented using computer software, the computer softwaremay be stored in a computer program product and loaded into computersystem 100 using inter alia a removable storage drive 114, an interface122, a hard drive 112 or a communications interface 134.

The invention is also directed to computer program products comprisingcomputer software stored on any computer usable medium. Such computersoftware, when executed in one or more data processing devices, causesthe data processing device(s) to operate as described herein.Embodiments of the invention employ any computer usable or readablemedium, known now or in the future. Examples of computer usable mediumsinclude, but are not limited to, primary storage devices (e.g., any typeof RAM), secondary storage devices (e.g., hard drives, floppy disks,Compact Disc Read-Only Memory [CD-ROM] disks, Zip disks, tapes, magneticstorage devices, optical storage devices, Microelectromechanical Systems[MEMS], nanotechnological storage device, etc.), and communicationmediums (e.g., wired and wireless communications networks, local areanetworks, wide area networks, intranets, etc.).

When it is used within a (Local Area Network (LAN), Wide Area Network(WAN), etc.) networking environment computer system 100 may be connected(by inter alia a wired connection or a wireless connection) to a networkthrough a network interface or adapter (such as inter alia an Ethernetcard) via communications interface 134. Under such a networkedenvironment, computer programs (computer control logic) may be stored,either in whole or in part, on one or more remote memory storage devices(in addition to the previously noted main memory 106 and/or secondarymemory 110).

FIG. 2 illustrates a computer software environment 200 that inter aliamay direct the operation of aspects of the computer system 100. Computersoftware environment 200, which may be stored inter alia in main memory(e.g., RAM) 106 and/or on secondary storage (e.g., a hard disk drive)110, includes inter alia an Operating System (OS) 204. The OS 204manages low-level aspects of computer operation including inter aliamanaging the execution of processes, memory allocation, file Inputand/or Output (I/O), and device I/O. One or more application programs,such as client application software or programs 202 (e.g., 202 a, 202 b,. . . 202 n) may be loaded (e.g., transferred from storage 110 into mainmemory 106) for execution by the computer system 100. The applicationsor other software intended for use on the computer system 100 may alsobe stored as a set of downloadable computer-executable instructions, forexample, for downloading and installation from an Internet location(e.g., an application server, World Wide Web (WWW) server, etc.).

Computer software environment 200 includes inter alia a Graphical UserInterface (GUI) 206 for receiving user commands and data in a graphical(e.g., a point-and-click) fashion. These inputs, in turn, may be actedupon by the computer system 100 in accordance with instructions from theOS 204 and/or client application software 202. The GUI 206 also servesto display the results of operation from the OS 204 and clientapplication software 202, whereupon the user may supply additionalinputs or terminate the session. Commonly the OS 204 operates inconjunction with possibly inter alia device drivers 208 and the systemBasic Input/Output System (BIOS) 210, particularly when interfacing withperipheral devices 212.

Popular examples of OS 204 include inter alia the different versions ofWindows from Microsoft®, the different versions of UNIX, the differentversions of Linux, etc.

The above-described computer system and computer software environmentwere presented for purposes of illustrating the basic underlying PC,server, etc. computer components that may be employed for implementingaspects of the present invention. It will be readily apparent to one ofordinary skill in the relevant art that numerous other components and/orcomponent arrangements are easily possible.

For simplicity of exposition the description below will at times presentexamples in which it will be assumed that there exists a “server” (e.g.,an application server, a WWW server, etc.) that inter alia communicateswith one or more “clients” (e.g., PCs; PDAs; handheld computers;cellular telephones, smart phones, and other wireless devices; etc.).The present invention, however, is not limited to any specificenvironment or device configuration. For example, a client/serverdistinction is not necessary to the invention but is used to provide aframework for discussion. To the contrary, the present invention may beimplemented in any type of information processing environment that iscapable of supporting the methodologies of the present invention asdescribed in detail below.

FIG. 3 and reference numeral 300 illustrate aspects of an informationprocessing environment comprising among other things one or more clients302 (which may include inter alia PCs; handheld computers; pagers; PDAs;cellular telephones, smart phones, and other wireless devices; radios;TVs; navigation systems; etc. 308) that are inter alia in communication,via a network 304, with a server 306. The server 306 comprises interalia a RDBMS 314.

At a high level clients 302 inter alia submit SQL statements 310 to theRDBMS 314, an engine 316 of the RDBMS evaluates and processes the SQLstatements 310, and the RDBMS 314 dispatches query results 312 to theclients 308.

The exemplary RDBMS engine 316 that is illustrated in FIG. 3 comprises:

1) A parser 318 that accepts a SQL statement 310 (e.g., as received froma client 302), processes (i.e., parses, etc.) the SQL statement 310, andproduces possibly inter alia a query tree.

2) A normalized 320 that processes a query tree to inter alia removeredundant operations, complete various error checking and othervalidation operations, etc.

3) A compiler 322 that (a) through an optimizer 324 identifies andevaluates for a query tree a range of query execution plans and thenselects a ‘best’ plan and (b) through a code generator 326 generates thecode necessary for the RDBMS to realize the selected ‘best’ plan.

4) An execution unit 328 that executes inter alia the generated code.

5) Various access methods 330 and a database store (page and indexmanager) 332 that support interactions with inter alia the differenttables 334 and indexes 338 that are maintained by the RDBMS 314 to amongother things arrive at the information 336 that is required to satisfy(reply to) the SQL statement 310.

The particular components and component arrangement that were depictedin FIG. 3 are illustrative only and it will be readily apparent to oneof ordinary skill in the relevant art that numerous other componentsand/or component arrangements are easily possible.

The present invention may reside within the optimizer component 324 of aRDBMS 314 where it can be used to inter alia enhance substantially theoperation, performance, etc. of an optimization algorithm (forsimplicity referred to below as ‘algorithm X’) such as for exampleDPhyp( ), MinCutHyp( ), TopDown( ), etc. (as offered by for example SQLAnywhere from Sybase®) that is executed within a RDBMS optimizer. Suchoptimization algorithms have several common characteristics includingpossibly inter alia (1) they exhaustively enumerate valid partitions ofthe form (S₁, S₂), corresponding to the logical join operation S₁

S₂, for a subset S=S₁ ∪ S₂ and preserve same in a memoization construct(referred to below as mTable[ ]), (2) generate a plan and then cost theplan for each partition, and (3) then complete various pruning andreview operations on the generated/casted plans to inter alia arrive ata ‘best’ plan.

Aspects of the present invention enhance substantially an algorithm X'soperation, performance, etc. by inter alia ordering the enumeratedpartitions and generating plans for only a fraction of the partitions,effectively pruning without costing the unpromising partitions. This isaccomplished through the preemption of the combined enumeration andcosting activity that algorithm X would traditionally complete by (1)saving, uncosted but in an ordered fashion, only some of the enumeratedpartitions in a memoization construct mTable, (2) generating a plan andthen costing the plan for only some subset of the saved partitions, and(3) then selecting a ‘best’ plan.

FIG. 4 and reference numeral 400 present, at a high level and in pseudocode, an algorithm ordered-Par( ) that encapsulates aspects of thepresent invention. The ordered-Par( ) algorithm divides the enumerationand plan generation process of optimization algorithm X into twoseparate and distinct phases:

1) Phase 1, an enumeration phase, preempts the combined enumeration andcosting of partitions that algorithm X would traditionally complete bysaving, (a) uncosted but (b) in an ordered fashion, only certain of theenumerated partitions for a subset S=S₁ ∪ S₂ in mTable[S] (referred toas Partitions(S)). FIG. 5 and reference numeral 500 depict, at a highlevel and in pseudo code, one possible EnumeratePartition( ) algorithmwith emphasis directed to two supporting functions—MaxParSize( ) 502 andScore( ) 504—that control the operation of this phase.

The supporting function MaxParSize(S) 502 identifies or defines, eitherstatically or dynamically, an allowed maximum size for the currentsubset S.

The supporting function Score(S₁, S₂) 504 may implement any number ofscoring models or paradigms including inter alia:

A) MinSel(S₁, S₂)=selectivity(p₁

. . .

p_(n)) where p₁→p_(n) are the join predicates which join the subsets S₁and S₂.

B) MinInput(S₁, S₂)=cardinality(S₁)+cardinality(S₂). This approachconsiders the estimated sizes of the input table expressions.

C) MinSets(S₁, S₂)=∥S₁∥−∥S₂∥. This approach, which considers thedifference between the number of relations of the two children (S₁ andS₂), relates the costing of the partitions to how balanced the left andright subtrees are.

2) Phase 2, a plan generation phase, where execution plans are generatedfor only a fraction of the saved partitions based on various constraintsso that inter alia unpromising partitions may be dropped (pruned). FIG.6 and reference numeral 600 depict, at a high level and in pseudo code,one possible GenerateBestPlan( ) algorithm with emphasis directed to twosupporting functions—Score( ) 504 (described above) and Budget( )602—that direct the operation of this phase.

The supporting function Budget( ) 602 defines possibly inter alia thedesired partition pruning strategy. Among other things the Budget( )function 602 has, during the execution life of the GenerateBestPlan( )function, dynamic access to full and complete knowledge of the searchspace (including inter alia the number of enumerated partitions, thenumber of partitions that have already been pruned, etc.) and as aresult the Budget( ) function 602 can among other things be dynamicallyadjusted, tuned, redefined, etc. For example, a Budget(S) function mightbe defined as Budget(S)=CurrentPruneFunction*|Partitions(S|

where:

A) CostPerPar is the estimated CPU time for costing a partition (asmeasured μs.

B) BestCost(V) is the best estimated cost found this far for a completeplan (as measured in μs).

C) TotalPar is the number of partitions that were saved during theenumeration phase.

D) CostedPar is the number of partitions that have already been costed.

E) PrunedPar is the number of partitions that were pruned withoutcosting.

F) CurrentTotalBudget=BestCost(V)/CostPerPar is the current maximumnumber of partitions to be costed.

G)CurrentPruneFunction=(CurrentTotalBudget−CostedPar)/(TotalPar−CostedPar−PrunedPar).

The different algorithms that were described above are illustrative onlyand it will be readily apparent to one of ordinary skill in the relevantart that various enhancements or modifications to an algorithm, numerousalternative algorithms, etc. are easily possible. For example and interalia:

1) Other join enumeration algorithms that possibly inter alia (a)enumerate partitions and (b) employ memoization to save access plans maybe substituted for algorithm X.

2) Two or more different join enumeration algorithms (that possiblyinter alia (a) enumerate partitions and (b) employ memoization to saveaccess plans) may be dynamically selected as an algorithm X duringexecution of an ordered-Par( ) algorithm.

3) In an EnumeratePartition( ) algorithm, sorting and/or pruning may beskipped if for example the search space is determined to be sparse.

4) Various enhanced system resource (e.g., memory, etc.) managementcapabilities may be implemented. As just one example, in theGenerateBestPlan( ) algorithm that is presented in FIG. 6 the memorythat was allocated to a Partitions(S) construct is explicitly releasedat the end of the routine.

5) Any combination of different pruning approaches (e.g., cost-based,etc.) may be employed in a GenerateBestPlan( ) algorithm.

6) Any number of different Budget( ) functions may be employed. Forexample, a Budget(S) function might be defined as Budget(S) ∈ {min(¼MaxParSize(S), |Partitions(S)|), 50%|Partitions(S)|100%|Partitions(S)|}.

7) A particular join enumeration algorithm (such as inter aliabacktracking as offered by for example Sybase's SQL Anywhere) may beexecuted first, before an algorithm X is invoked, to obtain inter aliaan initial or baseline cost.

FIG. 7 through FIG. 11 present various testing results that wereobtained during empirical testing of the ordered-Par( ) algorithm:

1) FIG. 7 and reference numeral 700 capture the results of a particularquery being optimized through two different algorithms, DPhyp( ) andordered-DPhyp( ) (the DPhyp( ) algorithm augmented with the ordered-Par() algorithm), where Score( )=MinSel( ), MaxParSize( )=5, andBudget(S)=CurrentPruneFunction*|Partitions(S)|.

2) FIG. 8 and reference numeral 800 capture two particular results(memory consumption and total cost) from a particular query beingoptimized through two different algorithms, DPhyp( ) and ordered-DPhyp() (the DPhyp( ) algorithm augmented with the ordered-Par( ) algorithm),where Score(S₁, S₂)=MinSel(S₁, S₂), MaxParSize(S) ∈ {1, 2, . . . , 20},and Budget(S) ∈ {min(¼ MaxParSize(S), |Partitions(S)|),50%|Partitions(S)|100%|Partitions(S)|}.

3) FIGS. 9→11 and reference numerals 900/1000/1100 capture twoparticular results (memory consumption and total cost) from a particularquery having three different shapes—chain, cycle, and star—beingoptimized through different algorithms (backtrackingM, DPhyp, MinCutHyp,and TopDown as offered by for example Sybase's SQL Anywhere) vs.ordered-DPhyp( ) (the DPhyp( ) algorithm augmented with the ordered-Par() algorithm), where Score(S₁, S₂) ∈ {MinSel(S₁, S₂), MinInput(S₁, S₂),MinSets(S₁, S₂)}, MaxParSize(S)=10, and Budget(S)=60%|Partitions(S)|.

In brief, during testing the ordered-DPhyp( ) algorithm exhibitedadvantageous characteristics (e.g., the costing of fewer partitions, theefficient consumption of memory, etc.) and yielded lower optimizationtimes.

The technology that was described above may be implemented as logicaloperations and/or modules in or across one or more computer systems. Thelogical operations may be implemented as a sequence ofprocessor-implemented steps executing in one or more computer systemsand as interconnected machine or circuit modules within one or morecomputer systems. Likewise, the descriptions of various componentmodules may be provided in terms of operations executed or effected bythe modules. The resulting implementation details are a matter ofchoice, dependent on the performance requirements of the underlyingsystem implementing the described technology. Accordingly, the logicaloperations making up the embodiments of the technology described aboveare referred to variously as operations, steps, objects, or modules.Furthermore, it should be understood that logical operations may beperformed in any order, unless explicitly claimed otherwise or unless aspecific order is inherently necessitated by the claim language.

The above specification provides a complete description of the structureand use of exemplary embodiments of the present invention. Althoughvarious embodiments of the invention have been described above with acertain degree of particularity, or with reference to one or moreindividual embodiments, it is to be understood that such material isexemplary only and it is not intended to be exhaustive or to limit theinvention to the specific forms disclosed. Those skilled in the artcould inter alia make numerous alterations to the disclosed embodimentswithout departing from the spirit or scope of this invention. Changes indetail or structure may be made without departing from the basicelements of the invention as defined in the appended claims.

Although the subject matter has been described in language specific tostructural features and/or methodological arts, it is to be understoodthat the subject matter defined in the appended claims is notnecessarily limited to the specific features or acts described above.Rather, the specific features and acts described above are disclosed asexample forms of implementing the claimed subject matter.

The following list defines acronyms as used in this disclosure:

Acrony01 Meaning BIOS Basic Input/Output System CD-ROM Compact Disc ReadOnly Memory DBMS Database Management System DRAM Dynamic Random AccessMemory EPROM Erasable Programmable Read-Only Memory GUI Graphical UserInterface I/O Input/Output LAN Local Area Network MEMSMicroelectromechanical Systems OS Operating System PC Personal ComputerPCMCIA Personal Computer Memory Card International Association PDAPersonal Digital Assistant PROM Programmable Read-Only Memory RAM RandomAccess Memory RDBMS Relational Database Management System RF RadioFrequency SQL Structured Query Language WAN Wide Area Network WWW WorldWide Web

1. (canceled)
 2. A computer-implemented method for selecting an optimal execution plan for a database request, the method comprising: processing aspects of the database request, including at least: enumerating certain of all possible valid logical plans, based on at least a maximum search space size, yielding enumerated logical plans, preserving, uncosted, certain or the enumerated logical plans, yielding a candidate search space, ordering entries in the candidate search space based on at least a dynamic scoring function, yielding a candidate search space order, and generating a physical plan for certain of the entries in the candidate search space, yielding generated physical plans, wherein the generating proceeds according to the candidate search space order and a dynamic budget function so as to efficiently drop unpromising entries; and reviewing a subset of the generated physical plans to select an optimal execution plan.
 3. The method of claim 2, wherein the database request includes a Structured Query Language statement.
 4. The method of claim 2, wherein the dynamic scoring function evaluates a logical property of the enumerated logical plans.
 5. The method of claim 2, wherein the dynamic scoring function evaluates a characteristic of one or more join predicates.
 6. The method of claim 2, wherein the dynamic scoring function evaluates a size of one or more database objects.
 7. The method of claim 6, wherein the one or more database objects include at least one database table.
 8. The method of claim 2, wherein the dynamic budget function evaluates two or more of a logical property of the enumerated logical plans, a physical property of the generated physical plans, and a characteristic of the candidate search space.
 9. The method of claim 2, wherein the dynamic budget function evaluates one or more of a size of the candidate space, a number of costed plans, and a number of uncosted plans.
 10. The method of claim 2, wherein the processing step and the reviewing step reside within a join enumeration algorithm that at least enumerates a plurality of logical plans and generates a plurality physical plans.
 11. A computer readable medium having instructions stored thereon that when executed by a processor, cause the processor to perform operations for selecting an optimal execution plan for a database request, the operations comprising: processing aspects of the database request, including at least: enumerating certain of all possible valid logical plans, based on at least a maximum search space size, yielding enumerated logical plans, preserving, uncosted, certain of the enumerated logical plans, yielding a candidate search space, ordering entries in the candidate search space based on at least a dynamic scoring function, yielding a candidate search space order, and generating a physical plan for certain of the entries in the candidate search space, yielding generated physical plans, wherein the generating proceeds according to the candidate search space order and a dynamic budget function so as to efficiently drop unpromising entries; and reviewing a subset of the generated physical plans to select an optimal execution plan.
 12. The computer readable medium of claim 11, wherein the database request includes a Structured Query Language statement.
 13. The computer readable medium of claim 11, wherein the dynamic scoring function evaluates a logical property of the enumerated logical plans.
 14. The computer readable medium of claim 11, wherein the dynamic scoring function evaluates a characteristic of one or more join predicates.
 15. The computer readable medium of claim 11, wherein the dynamic scoring function evaluates a size of one or more database objects.
 16. The computer readable medium of claim 15, wherein the one or more database objects include at least one database table.
 17. The computer readable medium of claim 11, wherein the dynamic budget function evaluates two or more of a logical property of the enumerated logical plans, a physical property of the generated physical plans, and a characteristic of the candidate search space.
 18. The computer readable medium of claim 11, wherein the dynamic budget function evaluates one or more of a size of the candidate space, a number of costed plans, and a number of uncosted plans.
 19. The computer readable medium of claim 11, wherein the processing step and the reviewing step reside within a join enumeration algorithm that at least enumerates a plurality of logical plans and generates a plurality physical plans.
 20. A system comprising: one or more processors; one or more computer readable media; and computer readable instructions stored on the one or more computer readable media that when executed by the one or more processors, cause the one or more processors to perform operations for selecting an optimal execution plan for a database request by: processing aspects of the database request, including at least: enumerating certain of all possible valid logical plans, based on at least a maximum search space size, yielding enumerated logical plans, preserving, uncosted, certain of the enumerated logical clans, yielding a candidate search space, ordering entries in the candidate search space based on at least a dynamic scoring function, yielding a candidate search space order, generating a physical plan for certain of the entries in the candidate search space, yielding generated physical plans, wherein the generating proceeds according to the candidate search space order and a dynamic budget function so as to efficiently drop unpromising entries, and reviewing a subset of the generated physical plans to select an optimal execution plan.
 21. The system of claim 20, wherein the database request includes a Structured Query Language statement.
 22. The system of claim 20, wherein the dynamic scoring function evaluates a logical property of the enumerated logical plans.
 23. The system of claim 20, wherein the dynamic scoring function evaluates a characteristic of one or more join predicates.
 24. The system of claim 20, wherein the dynamic scoring function evaluates a size of one or more database objects.
 25. The system of claim 24, wherein the one or more database objects include a database table.
 26. The system of claim 20, wherein the dynamic budget function evaluates two or more of a logical property of the enumerated logical plans, a physical property of the generated physical plans, and a characteristic of the candidate search space.
 27. The system of claim 20, wherein the dynamic budget function evaluates one or more of a size of the candidate space, a number of costed plans, and a number of uncosted plans.
 28. The system of claim 20, wherein the processing step and the reviewing step reside within a join enumeration algorithm that at least enumerates a plurality of logical plans and generates a plurality physical plans.
 29. A system comprising: one or more processors; a memory coupled to at least one of the one or more processors; and a query optimizer residing in the memory that, when executed by at least one of the one or more processors: enumerates, for a database request, certain of all possible valid logical plans, based on at least a maximum search space size, yielding enumerated logical plans; preserves, uncosted, certain of the enumerated logical plans, yielding a candidate search space; orders entries in the candidate search space based on at least a dynamic scoring function, yielding a candidate search space order, generates a physical plan for certain of the entries in the candidate search space, yielding generated physical plans, wherein the generating proceeds according to the candidate search space order and a dynamic budget function so as to efficiently drop unpromising entries; and reviews a subset of the generated physical plans to select an optimal execution plan. 